CREATE DATABASE RecrutimentStore
GO
USE RecrutimentStore

GO
CREATE TABLE Departments
(
	DepartmentID varchar(5) primary key not null,
	DepartmentName varchar(30) not null,
	DeStatus int not null
)
GO
insert into Departments values('D0001','HRD',1)
insert into Departments values('D0002','ITV',1)

go

CREATE TABLE Employees
(
	EmpNo varchar(30) primary key not null,
	EmpName varchar(50), 
	DepartmentID varchar(5) foreign key references Departments(DepartmentID) not null,
	Password varchar(30) not null,
	Address varchar(50),
	Phone varchar(15),
	Email varchar(30),	
	EmStatus int not null,
)
GO
select * from employees
insert into Employees values('E0001','Thang','D0001','123456','Hai Phong','01677239979','babyhachi@gmail.com',1)
insert into Employees values('E0002','Toan','D0002','123456','Ha Noi','01677239979','babyhachi@gmail.com',1)

CREATE TABLE Vacances
(
	VacancyID varchar(5) primary key not null,
	DepartmentID varchar(5) foreign key references Departments(DepartmentID) not null, 
	VacantyTitle varchar(100)not null ,
	NumOfJob int not null,
	Salary varchar(20) not null,
	Experience varchar(20) not null,
	StartDate datetime default getdate(), --defaut as today
	CloseDate datetime,
	VaStatus int not null --Open/Close/Suspended
)
select * from Vacances
GO
insert into Vacances values('V0001','D0002','Software Engineer (Mobile Developer)',5,'500 - 700 USD','2 years',
default,'11/15/2011',1)
insert into Vacances values('V0002','D0002','Senior Business Analyst (Web Development)',10,'500 - 700 USD','2 years',
default,'11/15/2011',1)
insert into Vacances values('V0003','D0002','Software Maintenance - Operation Leader.',2,'500 - 700 USD','2 years',
default,'11/15/2011',1)
insert into Vacances values('V0004','D0002','Senior Graphic Designer ',8,'500 - 700 USD','2 years',
default,'11/15/2011',1)
insert into Vacances values('V0005','D0002','Bridge Software Engineer (BSE)',5,'500 - 700 USD','2 years',
default,'11/15/2011',1)
insert into Vacances values('V0006','D0002','.NET Software Engineer ',5,'500 - 700 USD','2 years',
default,'11/15/2011',1)
insert into Vacances values('V0007','D0002','IT Deputy General Manager ',5,'500 - 700 USD','2 years',
default,'11/15/2011',1)
insert into Vacances values('V0008','D0002','Communicator Cum Japanese Translator',5,'500 - 700 USD','2 years',
default,'11/15/2011',1)
insert into Vacances values('V0009','D0002','.Net Developer',5,'500 - 700 USD','2 years',
default,'11/15/2011',1)
insert into Vacances values('V0010','D0002','QA/Tester',5,'500 - 700 USD','2 years',
default,'11/15/2011',1)
insert into Vacances values(' ','D0002','QA/Tester',5,'500 - 700 USD','2 years',
default,'11/15/2011',1)
delete from Vacances where 
go


go
CREATE TABLE Applicants
(
	ApplicantID varchar(5) primary key not null,	
	ApplicantName varchar(50) not null,
	AppPhone varchar(20) not null,
	AppEmail varchar(30) not null,
	AppAbout text not null,
	DOC datetime default getdate(), -- Date of create default as today	
	AppStatus int not null --Not in Process/In Process/Hired/Banned.
)
select * from Interviews
go
insert into Applicants values ('A0001','Nguyen van minh','123456789','minh@gmail.com','minh','11/01/2011',1 )

insert into Applicants values ('A0002','Nguyen van chien','12533434','chien@gmail.com','chien','10/01/2011',1 )
insert into Applicants values ('A0003','Nguyen van tien','343545654','tien@gmail.com','tien','08/11/2011',1 )
insert into Applicants values ('A0004','Dao van minh','786524323','minh@yahoo.com','minh','11/01/2011',1 )
insert into Applicants values ('A0005','Nguyen minh son','123456789','son@gmail.com','son','11/01/2011',1 )
insert into Applicants values ('A0006','Tran van tien','453456672','tien@gmail.com','tien','10/11/2011',1 )
insert into Applicants values ('A0007','Do thi nga','12342235','nga@gmail.com','minh','01/11/2011',1 )
insert into Applicants values ('A0008','Nguyen van ba','56553323','ba@gmail.com','minh','05/14/2011',1 )
insert into Applicants values ('A0009','Le thi huong','93334323','huong@gmail.com','minh','07/05/2011',1 )
go

CREATE TABLE Interviews
(
	VacancyID varchar(5) foreign key references Vacances(VacancyID) not null,
	ApplicantID varchar(5) foreign key references Applicants(ApplicantID) not null ,
	EmpNo varchar(30) foreign key references Employees(EmpNo) not null,	
	ScheduleDate datetime,
	AttachedDate datetime,
	StartTime varchar(20),
	EndTime varchar(20),
	primary key(VacancyID,ApplicantID),
	InStatus int not null --Scheduled/Selected/Rejected/Not Required
)
go
insert into Interviews values ('V0001','A0001','E0001',11/01/2011,11/10/2011,'null','null',1)
insert into Interviews values ('V0002','A0002','E0002',10/22/2011,10/30/2011,'null','null',1)
--insert into Interviews values ('V0003','A0003','E0003',06/21/2011,06/31/2011,'null','null',1)
--insert into Interviews values ('V0004','A0004','E0004',02/11/2011,02/20/2011,'null','null',1)
--insert into Interviews values ('V0005','A0005','E0005',02/11/2011,02/20/2011,'null','null',1)

create trigger dbUpdateVacances on Vacances for insert as
	declare @VacancyID varchar(5)
	declare @closeDate datetime;
	select @closeDate = CloseDate from inserted
	if @closeDate <= getdate()
		begin
			update Vacances set VaStatus = 2 where VacancyID = @VacancyID
		end  
go

create trigger dbStatusVacances on Interviews for insert as 
	declare @VacancyID varchar(5)
	declare @count nvarchar(30);
	select @VacancyID = VacancyID from inserted
	
	select @count =  count(*) from Interviews where VacancyID = @VacancyID and InStatus = 2
	if @count =5
		begin
			update Vacances set VaStatus = 2 where VacancyID = @VacancyID
		end
	else if @count <5
		begin
			update Vacances set VaStatus = 1 where VacancyID = @VacancyID
		end
go
--Trigger for insert to Vacances table
create trigger dbVacances on Vacances for insert as
declare @tam nvarchar(8)
declare @in nvarchar(8);
select @tam = count(*) from Vacances
select @in = VacancyID from inserted

if @tam >=0 and @tam <=9
begin 
	update Vacances set VacancyID = 'V000' + @tam where VacancyID = @in
end
else if @tam >=10 and @tam <=99
begin
	update Vacances set VacancyID = 'V00' + @tam where VacancyID = @in
end
else if @tam >=100 and @tam <=999
begin
	update Vacances set VacancyID = 'V0' + @tam where VacancyID = @in
end
else if @tam >=1000
begin
	update Vacances set VacancyID = 'V' + @tam where VacancyID = @in
end
else 
begin 
	print 'can not insert data' 
	rollback
end
select * from vacances
go

--Trigger for insert to Apllicants table
create trigger dbApplicants on Applicants for insert as
declare @tam nvarchar(8)
declare @in nvarchar(8);

select @tam = count(*) from Applicants
select @in = ApplicantID from inserted

if @tam >=0 and @tam <=9
begin 
	update Applicants set ApplicantID = 'A000' + @tam where ApplicantID = @in
end
else if @tam >=10 and @tam <=99
begin
	update Applicants set ApplicantID = 'A00' + @tam where ApplicantID = @in
end
else if @tam >=100 and @tam <=999
begin
	update Applicants set ApplicantID = 'A0' + @tam where ApplicantID = @in
end
else if @tam >=1000
begin
	update Applicants set ApplicantID = 'A' + @tam where ApplicantID = @in
end
else 
begin 
	print 'can not insert data' 
	rollback
end

select * from vacances